﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DAL
{
    [Serializable]
    public class LinkedDataCollection : System.Collections.Generic.IList<BLL.LinkedDataBLL>
    {
        #region Private Fields

        private List<BLL.LinkedDataBLL> linkeddatalist = new List<DAL.BLL.LinkedDataBLL>();

        private bool isreadonly = false;

        private string linkedDataSQLQuery = "SELECT [Ordinal], [Data], [Description], [Parameter1], [Parameter2], [Runtime], [PostDate], [Source], [Module] FROM [LinkedData] WHERE [TrailerId] = '{0}' AND [Type] = '{1}' ORDER BY [Ordinal]";
        private string trailersnotdownloadedSQL = "SELECT DISTINCT t.TrailerId AS Trailer, ld.TrailerId, ld.Type, ld.Data, ld.Parameter1, ld.Parameter2, ld.Runtime, ld.PostDate, ld.Source, ld.Ordinal, ld.Module, ld.Description FROM LinkedData AS ld LEFT OUTER JOIN Trailer AS t ON ld.TrailerId = t.TrailerId WHERE (ld.Type = 'preview') AND ((ld.Parameter1 + '-' + ld.Description) NOT IN (SELECT Type + '-' + Description AS Expr1 FROM Download AS d WHERE (ld.TrailerId = TrailerId))) ORDER BY Trailer, ld.Ordinal";

        private Data.DataAccess dataaccess = null;

        private string type = string.Empty;

        #endregion

        public LinkedDataCollection(string Type)
        {
            if (Type.Length == 0)
            {
                throw new ArgumentNullException("Type");
            }

            this.type = Type;
        }


        #region Public Members

        public int Count
        {
            get { return this.linkeddatalist.Count; }
        }


        #region Find

        public List<DAL.BLL.LinkedDataBLL> MatchesConfiguration(string Type, string Parameter1, string Description)
        {
            return this.linkeddatalist.FindAll(delegate(DAL.BLL.LinkedDataBLL LinkedData) { return LinkedData.Type == Type && LinkedData.Parameter1 == Parameter1 && LinkedData.Description == Description; });
        }

        public List<DAL.BLL.LinkedDataBLL> MatchesType(string Type)
        {
            return this.linkeddatalist.FindAll(delegate(DAL.BLL.LinkedDataBLL LinkedData) { return LinkedData.Type == Type; });
        }

        public List<DAL.BLL.LinkedDataBLL> MatchesSource(string Source)
        {
            return this.linkeddatalist.FindAll(delegate(DAL.BLL.LinkedDataBLL LinkedData) { return LinkedData.Source == Source; });
        }

        public DAL.BLL.LinkedDataBLL MatchesParameters(string Parameter1, string Parameter2)
        {
            return this.linkeddatalist.Find(delegate(DAL.BLL.LinkedDataBLL LinkedData) { return LinkedData.Parameter1 == Parameter1 && LinkedData.Parameter2 == Parameter2; });
        }

        #endregion


        public void Add(BLL.LinkedDataBLL item)
        {
            if (item == null)
            {
                throw new ArgumentNullException("item");
            }

            this.linkeddatalist.Add(item);
        }


        #region Save

        public bool Save(System.Guid TrailerId, Data.DataAccess DataAccess)
        {
            this.dataaccess = DataAccess;

            return this.Save(TrailerId);
        }

        public bool Save(System.Guid TrailerId)
        {
            if (TrailerId == default(System.Guid))
            {
                throw new ArgumentNullException("TrailerId");
            }

            if (this.dataaccess == null)
            {
                this.dataaccess = new Data.DataAccess();
            }

            string sql = this.BuildLinkedDataDeleteStatement(TrailerId, this.type);

            int ra = this.dataaccess.RunExecuteNonQuery(sql);

            if (linkeddatalist.Count > 0)
            {
                sql = this.BuildLinkedDataInsertStatement(TrailerId);

                ra = this.dataaccess.RunExecuteNonQuery(sql);
            }

            return true;
        }

        public bool Save(System.Guid TrailerId, string Source, Data.DataAccess DataAccess)
        {
            this.dataaccess = DataAccess;

            return this.Save(TrailerId, Source);
        }

        public bool Save(System.Guid TrailerId, string Source)
        {
            if (TrailerId == default(System.Guid))
            {
                throw new ArgumentNullException("TrailerId");
            }

            if (Source.Length == 0)
            {
                throw new ArgumentNullException("Source");
            }

            if (this.dataaccess == null)
            {
                this.dataaccess = new Data.DataAccess();
            }

            if (this.type == "preview")
            {
                return this.SavePreview(TrailerId, Source);
            }

            string sql = this.BuildLinkedDataDeleteStatement(TrailerId, this.type, Source);

            int ra = this.dataaccess.RunExecuteNonQuery(sql);

            if (linkeddatalist.Count > 0)
            {
                sql = this.BuildLinkedDataInsertStatement(TrailerId);

                ra = this.dataaccess.RunExecuteNonQuery(sql);
            }

            return true;
        }

        private bool SavePreview(System.Guid TrailerId, string Source)
        {
            string sql = string.Empty;
            int ra = 0;

            foreach (BLL.LinkedDataBLL linkedDataItem in this.linkeddatalist)
            {
                sql = this.BuildLinkedDataDeleteStatement(linkedDataItem.Source, linkedDataItem.Module, this.type, linkedDataItem.Data, linkedDataItem.Parameter1, linkedDataItem.Description, TrailerId);

                ra = this.dataaccess.RunExecuteNonQuery(sql);

                sql = this.BuildSingleLinkedDataInsertStatement(TrailerId, linkedDataItem);

                ra = this.dataaccess.RunExecuteNonQuery(sql);
            }

            return true;
        }

        #endregion


        #region Populate

        public void PopulateLinkedData(string TrailerId, Data.DataAccess DataAccess)
        {
            this.dataaccess = DataAccess;

            this.PopulateLinkedData(TrailerId);
        }

        public void PopulateLinkedData(string TrailerId)
        {
            if (TrailerId.Length == 0)
            {
                throw new ArgumentNullException("TrailerId");
            }

            if (this.dataaccess == null)
            {
                this.dataaccess = new Data.DataAccess();
            }

            this.linkeddatalist.Clear();

            System.Data.DataTable linkedData = new System.Data.DataTable();

            string sql = string.Format(this.linkedDataSQLQuery, TrailerId, this.type);

            linkedData = this.dataaccess.RunFill(sql);

            BLL.LinkedDataBLL linkedDataItem = null;
            foreach (System.Data.DataRow data in linkedData.Rows)
            {
                linkedDataItem = new DAL.BLL.LinkedDataBLL(data["Source"].ToString(), data["Module"].ToString(), this.type, data["Data"].ToString());

                if (data["Description"].ToString().Length != 0)
                {
                    linkedDataItem.Description = data["Description"].ToString();
                }

                if (data["Ordinal"].ToString().Length != 0)
                {
                    linkedDataItem.Ordinal = System.Convert.ToInt64(data["Ordinal"].ToString());
                }

                if (data["Parameter1"].ToString().Length != 0)
                {
                    linkedDataItem.Parameter1 = data["Parameter1"].ToString();
                }

                if (data["Parameter2"].ToString().Length != 0)
                {
                    linkedDataItem.Parameter2 = data["Parameter2"].ToString();
                }

                if (data["Runtime"].ToString().Length != 0)
                {
                    linkedDataItem.Runtime = data["Runtime"].ToString();
                }

                if (data["PostDate"].ToString().Length != 0)
                {
                    linkedDataItem.PostDate = System.Convert.ToDateTime(data["PostDate"].ToString());
                }

                this.linkeddatalist.Add(linkedDataItem);
            }
        }

        public void PopulateTrailersNotDownlaoded()
        {
            if (this.dataaccess == null)
            {
                this.dataaccess = new Data.DataAccess();
            }

            this.linkeddatalist.Clear();

            System.Data.DataTable linkedData = new System.Data.DataTable();

            string sql = this.trailersnotdownloadedSQL;

            linkedData = this.dataaccess.RunFill(sql);

            BLL.LinkedDataBLL linkedDataItem = null;
            foreach (System.Data.DataRow data in linkedData.Rows)
            {
                linkedDataItem = new DAL.BLL.LinkedDataBLL(data["Source"].ToString(), data["Module"].ToString(), this.type, data["Data"].ToString());

                if (data["TrailerId"].ToString().Length != 0)
                {
                    linkedDataItem.TrailerId = new Guid(data["TrailerId"].ToString());
                }

                if (data["Description"].ToString().Length != 0)
                {
                    linkedDataItem.Description = data["Description"].ToString();
                }

                if (data["Ordinal"].ToString().Length != 0)
                {
                    linkedDataItem.Ordinal = System.Convert.ToInt64(data["Ordinal"].ToString());
                }

                if (data["Parameter1"].ToString().Length != 0)
                {
                    linkedDataItem.Parameter1 = data["Parameter1"].ToString();
                }

                if (data["Parameter2"].ToString().Length != 0)
                {
                    linkedDataItem.Parameter2 = data["Parameter2"].ToString();
                }

                if (data["Runtime"].ToString().Length != 0)
                {
                    linkedDataItem.Runtime = data["Runtime"].ToString();
                }

                if (data["PostDate"].ToString().Length != 0)
                {
                    linkedDataItem.PostDate = System.Convert.ToDateTime(data["PostDate"].ToString());
                }

                this.linkeddatalist.Add(linkedDataItem);
            }
        }

        #endregion

        #endregion

        #region Private Members


        #region Select

        private string BuildPreviewSelectCountStatement(System.Guid TrailerId, DAL.BLL.LinkedDataBLL LinkedDataItem)
        {
            return string.Format("SELECT COUNT([DownloadId]) FROM [Download] WHERE [TrailerId] = '{0}' AND [Type] = '{1}' AND [Description] = '{2}' AND [Source] = '{3}'", TrailerId, LinkedDataItem.Parameter1, LinkedDataItem.Description, LinkedDataItem.Source);
            //return string.Format("SELECT COUNT([LinkedDataID]) From [LinkedData] WHERE [TrailerId] = '{0}' AND [Type] = '{1}' AND [Parameter1] = '{2}' AND [Parameter2] = '{3}' AND [Source] = '{4}' AND [Module] = '{5}' AND [Description] = '{6}'", TrailerId, this.type, LinkedDataItem.Parameter1, LinkedDataItem.Parameter2, LinkedDataItem.Source, LinkedDataItem.Module, LinkedDataItem.Description);
        }

        #endregion


        #region Insert

        private string BuildLinkedDataInsertStatement(System.Guid TrailerId)
        {
            StringBuilder insertSQL = new StringBuilder();
            StringBuilder insertValues = new StringBuilder();

            //INSERT INTO phone_book
            //SELECT 'John Doe', '555-1212'
            //UNION ALL
            //SELECT 'Peter Doe', '555-2323';

            insertSQL.Append("INSERT INTO [LinkedData] ([TrailerId], [Ordinal], [Type], [Data], [Source], [Module]) ");

            foreach (BLL.LinkedDataBLL linkedDataItem in this.linkeddatalist)
            {
                if (insertValues.Length > 0)
                {
                    insertValues.Append(" UNION ALL ");
                }

                insertValues.Append(string.Format("SELECT '{0}', {1}, '{2}', '{3}', '{4}', '{5}'", TrailerId.ToString(), linkedDataItem.Ordinal.ToString(), linkedDataItem.Type, linkedDataItem.Data.Replace("'", "''"), linkedDataItem.Source, linkedDataItem.Module));
            }

            insertSQL.Append(insertValues.ToString());

            return insertSQL.ToString();
        }

        private string BuildSingleLinkedDataInsertStatement(System.Guid TrailerId, DAL.BLL.LinkedDataBLL LinkedDataItem)
        {
            StringBuilder insertSQL = new StringBuilder();
            StringBuilder insertValues = new StringBuilder();

            insertSQL.Append("INSERT INTO [LinkedData] (");
            insertValues.Append(") VALUES (");

            insertSQL.Append("[TrailerId], [Ordinal], [Type], [Data], [Source], [Module]");
            insertValues.Append(string.Format("'{0}', {1}, '{2}', '{3}', '{4}', '{5}'", TrailerId.ToString(), LinkedDataItem.Ordinal.ToString(), LinkedDataItem.Type, LinkedDataItem.Data.Replace("'", "''"), LinkedDataItem.Source, LinkedDataItem.Module));

            if (LinkedDataItem.Parameter1.Length != 0)
            {
                insertSQL.Append(", [Parameter1]");

                insertValues.Append(string.Format(", '{0}'", LinkedDataItem.Parameter1.Replace("'", "''")));
            }

            if (LinkedDataItem.Parameter2.Length != 0)
            {
                insertSQL.Append(", [Parameter2]");

                insertValues.Append(string.Format(", '{0}'", LinkedDataItem.Parameter2.Replace("'", "''")));
            }

            if (LinkedDataItem.Description.Length != 0)
            {
                insertSQL.Append(", [Description]");

                insertValues.Append(string.Format(", '{0}'", LinkedDataItem.Description.Replace("'", "''")));
            }

            if (LinkedDataItem.Runtime.Length != 0)
            {
                insertSQL.Append(", [Runtime]");

                insertValues.Append(string.Format(", '{0}'", LinkedDataItem.Runtime.Replace("'", "''")));
            }

            if (LinkedDataItem.PostDate != default(DateTime))
            {
                insertSQL.Append(", [PostDate]");

                insertValues.Append(string.Format(", '{0}'", LinkedDataItem.PostDate.ToString("yyyy-MM-dd HH:mm:ss")));
            }

            insertValues.Append(")");

            insertSQL.Append(insertValues.ToString());

            return insertSQL.ToString();
        }

        private string BuildInsertSelectStatement(System.Guid TrailerId, DAL.BLL.LinkedDataBLL LinkedDataItem)
        {
            StringBuilder selectSQL = new StringBuilder();

            return selectSQL.ToString();
        }

        #endregion


        #region Delete

        private string BuildLinkedDataDeleteStatement(System.Guid TrailerId, string Type)
        {
            return string.Format("DELETE FROM [LinkedData] WHERE [TrailerId] = '{0}' AND [Type] = '{1}'", TrailerId.ToString(), Type);
        }

        private string BuildLinkedDataDeleteStatement(System.Guid TrailerId, string Type, string Source)
        {
            return string.Format("DELETE FROM [LinkedData] WHERE [TrailerId] = '{0}' AND [Type] = '{1}' AND [Source] = '{2}'", TrailerId.ToString(), Type, Source);
        }

        private string BuildLinkedDataDeleteStatement(string Source, string Module, string Type, string Data, string Parameter1, string Description, System.Guid TrailerId)
        {
            return string.Format("DELETE FROM [LinkedData] WHERE [Source] = '{0}' AND [Module] = '{1}' AND [Type] = '{2}' AND [Data] = '{3}' AND [Description] = '{4}' AND [Parameter1] = '{5}' AND [TrailerId] = '{6}'", Source, Module, Type, Data, Description, Parameter1, TrailerId.ToString());
        }

        #endregion

        #endregion

        #region IList<LinkedDataBLL> Members

        int IList<DAL.BLL.LinkedDataBLL>.IndexOf(DAL.BLL.LinkedDataBLL item)
        {
            return this.linkeddatalist.IndexOf(item);
        }

        void IList<DAL.BLL.LinkedDataBLL>.Insert(int index, DAL.BLL.LinkedDataBLL item)
        {
            this.linkeddatalist.Insert(index, item);
        }

        void IList<DAL.BLL.LinkedDataBLL>.RemoveAt(int index)
        {
            this.linkeddatalist.RemoveAt(index);
        }

        DAL.BLL.LinkedDataBLL IList<DAL.BLL.LinkedDataBLL>.this[int index]
        {
            get
            {
                return this.linkeddatalist[index];
            }
            set
            {
                this.linkeddatalist[index] = value;
            }
        }

        #endregion

        #region ICollection<LinkedDataBLL> Members

        void ICollection<DAL.BLL.LinkedDataBLL>.Add(DAL.BLL.LinkedDataBLL item)
        {
            this.linkeddatalist.Add(item);
        }

        void ICollection<DAL.BLL.LinkedDataBLL>.Clear()
        {
            this.linkeddatalist.Clear();
        }

        bool ICollection<DAL.BLL.LinkedDataBLL>.Contains(DAL.BLL.LinkedDataBLL item)
        {
            return this.linkeddatalist.Contains(item);
        }

        void ICollection<DAL.BLL.LinkedDataBLL>.CopyTo(DAL.BLL.LinkedDataBLL[] array, int arrayIndex)
        {
            this.linkeddatalist.CopyTo(array, arrayIndex);
        }

        int ICollection<DAL.BLL.LinkedDataBLL>.Count
        {
            get { return this.linkeddatalist.Count; }
        }

        bool ICollection<DAL.BLL.LinkedDataBLL>.IsReadOnly
        {
            get { return this.isreadonly; }
        }

        bool ICollection<DAL.BLL.LinkedDataBLL>.Remove(DAL.BLL.LinkedDataBLL item)
        {
            return this.linkeddatalist.Remove(item);
        }

        #endregion

        #region IEnumerable<LinkedDataBLL> Members

        IEnumerator<DAL.BLL.LinkedDataBLL> IEnumerable<DAL.BLL.LinkedDataBLL>.GetEnumerator()
        {
            return this.linkeddatalist.GetEnumerator();
        }

        #endregion

        #region IEnumerable Members

        System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
        {
            return this.linkeddatalist.GetEnumerator();
        }

        #endregion
    }
}
